﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;

namespace JSExcel
{

    public class WorkBook
    {
        public List<WorkSheet> WorkSheets;
        public Dictionary<string, Style> Styles;
        public int ActiveSheetIndex;
        [ScriptIgnore]
        public IWorkbook workbook = null;
        public WorkBook() {
            WorkSheets = new List<WorkSheet>();
            Styles = new Dictionary<string, Style>();
            ActiveSheetIndex = 0;
        }
        public void LoadExcel(string fileName) {
            FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            if (fileName.IndexOf(".xlsx") > 0) workbook = new XSSFWorkbook(fs);// 2007版本
            else if (fileName.IndexOf(".xls") > 0)workbook = new HSSFWorkbook(fs);// 2003版本
            fs.Close();
            ActiveSheetIndex = workbook.ActiveSheetIndex;
            for (short i = 0; i < workbook.NumCellStyles; i++)
            {
                Style style = new Style();
                style.LoadStyle(workbook, i);
                Styles.Add(i.ToString(), style);
            }
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                WorkSheet sheet = new WorkSheet();
                sheet.LoadSheet(workbook.GetSheetAt(i));
                WorkSheets.Add(sheet);
            }
        }
        public string ToJson()
        {
            return new JavaScriptSerializer().Serialize(this);
        }
    }


    public class WorkSheet
    {
        public string SheetName;
        public int ExpandedColumnCount;
        public int ExpandedRowCount;
        public float DefaultColumnWidth;
        public float DefaultRowHeight;
        public CellPoint ActiveCell;
        public Dictionary<string,Column> Columns;
        public Dictionary<string, Row> Rows;

        public List<Merge> Merges;
        public WorkSheet()
        {
            Columns = new Dictionary<string, Column>();
            Rows = new Dictionary<string, Row>();
            Merges = new List<Merge>();
           
        }
        public void LoadSheet(ISheet sheet)
        {
            SheetName = sheet.SheetName;
            DefaultColumnWidth = sheet.DefaultColumnWidth <1000 ? 72 : sheet.DefaultColumnWidth / 256 * 10 + 10;
            DefaultRowHeight = sheet.DefaultRowHeightInPoints+1;//.DefaultRowHeight / 20 * (4 / 3) ;
            ExpandedRowCount = sheet.LastRowNum + 2;
            int ColumnCount = 0;
            if(sheet is XSSFSheet)
            {
                string str=((XSSFSheet)sheet).ActiveCell;
                if (!string.IsNullOrEmpty(str))
                {
                    ActiveCell = new CellPoint();
                    ActiveCell.RowNum = PubFun.GetRowNum(str);
                    ActiveCell.CellNum = PubFun.GetCellNum(str);
                }
            }
           

            for (int i = sheet.FirstRowNum; i < sheet.LastRowNum; i++)
            {
                IRow r = sheet.GetRow(i);
                if (r == null) continue;
                if (r.LastCellNum > ColumnCount) ColumnCount = r.LastCellNum;
                 Row row = new Row();
                row.LoadRow(r);
                Rows.Add((i + 1).ToString(), row);
            }
            ExpandedColumnCount = ColumnCount + 1;

            for (int i = 0; i < ColumnCount; i++)
            {
                float w = sheet.GetColumnWidthInPixels(i);
                if (w > 0)
                {
                    Column column = new Column(w + 10);
                    Columns.Add((i + 1).ToString(), column);
                }
            }
            for (int i = 0; i < sheet.NumMergedRegions; i++)
            {
                Merge merge = new Merge();
                merge.LoadMerge(sheet.GetMergedRegion(i));
                Merges.Add(merge);
            }
        }
    }

    public class CellPoint
    {
        public int RowNum;
        public int CellNum;
        public CellPoint(int rowNum=0, int cellNum=0)
        {
            RowNum = rowNum;
            CellNum = cellNum;
        }
    }

    public class Merge 
    {
        public CellPoint StartCell;
        public CellPoint EndCell;
        public Merge()
        {
            StartCell = new CellPoint();
            EndCell = new CellPoint();
        }
        public void LoadMerge(CellRangeAddress range) {
            StartCell.RowNum = range.FirstRow + 1;
            StartCell.CellNum = range.FirstColumn + 1;
            EndCell.RowNum = range.LastRow + 1;
            EndCell.CellNum = range.LastColumn + 1;
        }
    }


    public class Column
    {
        public double Width;
        public Column(float width)
        {
            Width = width;
        }
    }


    public class Row
    {
        public double Height;
        public Dictionary<string, Cell> Cells;
        public Row()
        {
            Cells = new Dictionary<string, Cell>();
        }
        public void LoadRow(IRow row)
        {
            Height = row.HeightInPoints+2;//.Height/20*(4/3);
            for (int i = row.FirstCellNum; i < row.LastCellNum; i++)
            {
                ICell c = row.GetCell(i);
                if (c == null) continue;
                Cell cell = new Cell();
                cell.LoadCell(c);
                Cells.Add((i + 1).ToString(), cell);
            }
        }
    }

    public class Cell 
    {
        public string StyleID;
        public Data Data;
        public Cell()
        {
            Data = new Data("String","");
        }
        public void LoadCell(ICell cell)
        {
          if (cell.CellStyle!=null)StyleID = cell.CellStyle.Index.ToString();
            switch (cell.CellType) {
                case CellType.Numeric:
                    Data.Type = "Number";
                    Data.Value = cell.NumericCellValue.ToString();
                    break;
                case CellType.Boolean:
                    Data.Type = "Boolean";
                    Data.Value = cell.BooleanCellValue.ToString();
                    break;
                default:
                    Data.Type = "String";
                    Data.Value = cell.StringCellValue;
                    break;
            }
        }
    }

    public class Data
    {
        public string Type;
        public string Value;
        public Data(string type, string value)
        {
            Type = type;
            Value = value;
        }
    }

    public class Style
    {
        public Alignment Alignment;
        public Borders Borders;
        public Font Font;
        public Style()
        {
            Alignment = new Alignment();
            Borders = new Borders();
            Font = new Font();
        }
        public void LoadStyle(IWorkbook workbook,short styleIndex) {
            ICellStyle cellStyle = workbook.GetCellStyleAt(styleIndex);
            Alignment.LoadAlignment(cellStyle);
            Borders.LoadBorders(cellStyle);
            Font.LoadFont(cellStyle.GetFont(workbook), workbook);
        }
    }

    public class Alignment
    {
        public string Vertical;
        public string Horizontal;
        public Alignment()
        {
            Vertical= "middle";
            Horizontal="left";
        }
        public void LoadAlignment(ICellStyle cellStyle)
        {
            switch (cellStyle.VerticalAlignment) {
                case VerticalAlignment.Bottom:
                    Vertical = "bottom";
                    break;
                case VerticalAlignment.Top:
                    Vertical = "top";
                    break;
                default:
                    Vertical = "middle";
                    break;
            }
            switch (cellStyle.Alignment)
            {
                case HorizontalAlignment.Left:
                    Vertical = "left";
                    break;
                case HorizontalAlignment.Right:
                    Vertical = "right";
                    break;
                default:
                    Vertical = "center";
                    break;
            }
        }
    }

    public class Borders
    {
        public Border Border = null;
        public Border BorderTop = null;
        public Border BorderBottom = null;
        public Border BorderLeft = null;
        public Border BorderRight = null;
        public Borders()
        {

        }
        public void LoadBorders(ICellStyle cellStyle)
        {
            if (cellStyle.BorderTop != BorderStyle.None && cellStyle.BorderTop == cellStyle.BorderBottom
                && cellStyle.BorderTop == cellStyle.BorderLeft && cellStyle.BorderTop == cellStyle.BorderRight)
            {
                Border = new Border(1);
            }
            else
            {
                if (cellStyle.BorderTop != BorderStyle.None)
                {
                    BorderTop = new Border(1);
                }
                if (cellStyle.BorderBottom != BorderStyle.None)
                {
                    BorderBottom = new Border(1);
                }
                if (cellStyle.BorderLeft != BorderStyle.None)
                {
                    BorderLeft = new Border(1);
                }
                if (cellStyle.BorderRight != BorderStyle.None)
                {
                    BorderRight = new Border(1);
                }
            }
        }
    }
    public class Border
    {
        public string LineStyle;
        public int Weight;
        public string Color;
        public Border(int width,string lineStyle= "solid",string color="#000000")
        {
            LineStyle = lineStyle;
            Weight = width;
            Color = color;
        }
    }

    public class Font
    {
        public string FontName;
        public double Size;
        public string Color;
        public Font()
        {
            FontName = "宋体";
            Size = 11;
            Color = "#000000";
        }
        public void LoadFont(IFont font, IWorkbook workBook) {

            FontName = font.FontName;
            Size = font.FontHeightInPoints;

        }
    }
}